
using Youz.Common.DBUtility;
using Youz.CreditRepay.Entity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using Youz.Common.Helper;

namespace Youz.CreditRepay.DAL
{
    /// <summary>
    /// Member_AssetHistory Data Class
    /// </summary>
    public partial class Member_AssetHistoryDAL : BaseDAL
    {
        private DbHelperSQL DbHelperSQL = new DbHelperSQL();
        public string TableName = "Member_AssetHistory";

        public Member_AssetHistoryDAL()
        {}

        #region  成员方法
        /// <summary>
        ///  新增一条数据
        /// </summary>
        public bool Add(Member_AssetHistory model)
        {
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append("insert into [Member_AssetHistory](");
            strSql.Append("[HistoryID],[MemberAccount],[AssetType],[ChangeType],[ChangeAgoBalance],[ChangeBalance],[ChangeAfterBalance],[Memo],[ObjectID],[CreateTime],[Creator]");
            strSql.Append(") values (");
            strSql.Append("@HistoryID,@MemberAccount,@AssetType,@ChangeType,@ChangeAgoBalance,@ChangeBalance,@ChangeAfterBalance,@Memo,@ObjectID,@CreateTime,@Creator");
            strSql.Append(")");
            
            SqlParameter[] parameters = {
                  
                    new SqlParameter("@HistoryID", model.HistoryID),
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@AssetType", model.AssetType),
                    new SqlParameter("@ChangeType", model.ChangeType),
                    new SqlParameter("@ChangeAgoBalance", model.ChangeAgoBalance),
                    new SqlParameter("@ChangeBalance", model.ChangeBalance),
                    new SqlParameter("@ChangeAfterBalance", model.ChangeAfterBalance),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@ObjectID", model.ObjectID),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@Creator", model.Creator)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        ///  保存一条数据
        /// </summary>
        public bool Update(Member_AssetHistory model)
        {
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append("update [Member_AssetHistory] set ");
            strSql.Append("[MemberAccount]=@MemberAccount,[AssetType]=@AssetType,[ChangeType]=@ChangeType,[ChangeAgoBalance]=@ChangeAgoBalance,[ChangeBalance]=@ChangeBalance,[ChangeAfterBalance]=@ChangeAfterBalance,[Memo]=@Memo,[ObjectID]=@ObjectID,[CreateTime]=@CreateTime,[Creator]=@Creator");
            strSql.Append(" where [HistoryID]=@HistoryID");
            
            SqlParameter[] parameters = {
                  
                    new SqlParameter("@HistoryID", model.HistoryID),
                    new SqlParameter("@MemberAccount", model.MemberAccount),
                    new SqlParameter("@AssetType", model.AssetType),
                    new SqlParameter("@ChangeType", model.ChangeType),
                    new SqlParameter("@ChangeAgoBalance", model.ChangeAgoBalance),
                    new SqlParameter("@ChangeBalance", model.ChangeBalance),
                    new SqlParameter("@ChangeAfterBalance", model.ChangeAfterBalance),
                    new SqlParameter("@Memo", model.Memo),
                    new SqlParameter("@ObjectID", model.ObjectID),
                    new SqlParameter("@CreateTime", model.CreateTime),
                    new SqlParameter("@Creator", model.Creator)
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        ///</summary>
        public bool Delete(Int64 HistoryID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [Member_AssetHistory] ");
            strSql.Append(" where [HistoryID]=@HistoryID");
            
            SqlParameter[] parameters = {
            
                new SqlParameter("@HistoryID", HistoryID)             
              
            };

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_AssetHistory Get(Int64 HistoryID)
        {
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append("select  top 1 * from [Member_AssetHistory] ");
            strSql.Append(" where [HistoryID]=@HistoryID");
            
            SqlParameter[] parameters = {
            
                new SqlParameter("HistoryID", HistoryID)             
              
            };

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return Get(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Member_AssetHistory Get(DataRow row)
        {
            if (row == null) return null;

            Member_AssetHistory model = new Member_AssetHistory();
    
            
            if (row["HistoryID"] != DBNull.Value)
            {
                model.HistoryID = (Int64)row["HistoryID"];
            }      
            
            if (row["MemberAccount"] != DBNull.Value)
            {
                model.MemberAccount = (String)row["MemberAccount"];
            }      
            
            if (row["AssetType"] != DBNull.Value)
            {
                model.AssetType = (String)row["AssetType"];
            }      
            
            if (row["ChangeType"] != DBNull.Value)
            {
                model.ChangeType = (Int32)row["ChangeType"];
            }      
            
            if (row["ChangeAgoBalance"] != DBNull.Value)
            {
                model.ChangeAgoBalance = (Decimal)row["ChangeAgoBalance"];
            }      
            
            if (row["ChangeBalance"] != DBNull.Value)
            {
                model.ChangeBalance = (Decimal)row["ChangeBalance"];
            }      
            
            if (row["ChangeAfterBalance"] != DBNull.Value)
            {
                model.ChangeAfterBalance = (Decimal)row["ChangeAfterBalance"];
            }      
            
            if (row["Memo"] != DBNull.Value)
            {
                model.Memo = (String)row["Memo"];
            }      
            
            if (row["ObjectID"] != DBNull.Value)
            {
                model.ObjectID = (String)row["ObjectID"];
            }      
            
            if (row["CreateTime"] != DBNull.Value)
            {
                model.CreateTime = (DateTime)row["CreateTime"];
            }      
            
            if (row["Creator"] != DBNull.Value)
            {
                model.Creator = (String)row["Creator"];
            }      
            

            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet Gets(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            
            strSql.Append("select * from [Member_AssetHistory] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet Gets(int Top, string sqlWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" *  FROM [Member_AssetHistory] ");
            
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            if(filedOrder.Trim() != "")
            {
                strSql.Append(" order by " + filedOrder);
            }
            
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataTable Gets(int top, string showColumn, string sqlWhere, params SqlParameter[] cmdParms)
        {
            return DbHelperSQL.Gets(top, showColumn, "Member_AssetHistory", sqlWhere, "HistoryID", cmdParms);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string sqlWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM [Member_AssetHistory] ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable Gets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(" SELECT * FROM [Member_AssetHistory]  ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by HistoryID desc ", out count, cmdParms);
            total = count;
            return dt;
        }

        /// <summary>
        /// 获得数据列表分页
        /// </summary>
        public DataTable BillGets(string sqlWhere, int pageindex, int pagesize, out int total, params SqlParameter[] cmdParms)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append(@" select * from (
                              SELECT [ID] as [BillID],[MemberAccount],'还款' as [MoneyType],[BillingAmount] as [Amount],[Fee],[Status],[CreatedOn] as [CreateTime] FROM [dbo].[CR_RepaymentPlan]
                              UNION ALL
                              SELECT [WithdrawID] as [BillID],[MemberAccount],'取现' as [MoneyType],[WithdrawAmount] as [Amount],[WithdrawFee] as [Fee],[Status],[CreateTime] FROM [dbo].[Member_Withdraw]
                              UNION ALL
                              SELECT [RechargeID] as [BillID],[MemberAccount],'充值' as [MoneyType],[FromAmount] as [Amount],[Fee],[Status],[CreateTime] FROM [dbo].[Member_Recharge]
                              UNION ALL
                              SELECT [BonusID] as [BillID],[MemberAccount],case [BonusType] when 1 then '新人推荐' when 2 then '利息分红' else '未知' end as [MoneyType],[Bonus] as [Amount],0 as [Fee],1 as [Status],[CreateTime] FROM [dbo].[Member_Bonus]
                               ) a  ");
            if (sqlWhere.Trim() != "")
            {
                strSql.Append(" where " + sqlWhere);
            }

            int count = 0;
            DataTable dt = DbHelperSQL.GetPager(pageindex, pagesize, "" + strSql + "", "order by [CreateTime] desc ", out count, cmdParms);
            total = count;
            return dt;
        }
        #endregion  成员方法

        /// <summary>
        /// 生成ID
        /// </summary>
        /// <returns></returns>
        //public Int64 GenerateID()
        //{
        //    var id = Convert.ToInt64(DbHelperSQL.GetSingle("select isnull(max(HistoryID),0) from [dbo].[Member_AssetHistory]"));

        //    return id == 0 ? 10000000 : id + 1;
        //}
        /// <summary>
        /// 操作历史明细
        /// </summary>
        /// <param name="memberAccount"></param>
        /// <param name="operateMember"></param>
        /// <param name="objID"></param>
        /// <param name="changeType"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        public string GetSqlByOperateAsset(string memberAccount, string operateMember, string objID, AssetHistoryChangeType changeType, List<AssetHistoryItem> list)
        {
            StringBuilder sb = new StringBuilder();
            StringBuilder sbLog = new StringBuilder();
            string y = string.Empty, n = string.Empty, key = string.Empty;
            string tpSqlHistory = @" INSERT INTO [dbo].[Member_AssetHistory] ([MemberAccount],[AssetType],[ChangeType],[ChangeAgoBalance],[ChangeBalance],[ChangeAfterBalance],[Memo],[ObjectID],[Creator],[CreateTime]) 
                VALUES ('" + memberAccount + "','{0}','" + ((int)changeType) + "',{1},{2},{3},'','" + objID + "','" + operateMember + "',getdate()) ";
            Member_Account asset = new Member_AccountDAL().Get(memberAccount);

            if (asset == null) return string.Empty;

            sb.Append(" UPDATE [dbo].[Member_Account] SET ");

            decimal beforeBalance = 0, changeBalance = 0, afterBalance = 0;
            foreach (var item in list)
            {
                key = item.HistoryType.ToString();
                changeBalance = item.AssetValue;

                if (changeBalance == 0) continue;

                beforeBalance = (decimal)GetProperty(asset, key);

                switch (item.OperateType)
                {
                    case AssetOperateType.Minus:
                        y = "-";
                        break;
                    case AssetOperateType.Plus:
                        y = "+";
                        break;
                }

                sb.AppendFormat(" {0} = (case when {2} > 0.01 and {0} {1} {2} <= 0.01 and {0} {1} {2} >= -0.01 then 0 else {0} {1} {2} end), ", key, y, changeBalance);

                afterBalance = y == "-" ? beforeBalance - changeBalance : beforeBalance + changeBalance;
                if (changeBalance > 0.01M && afterBalance <= 0.01M && afterBalance >= -0.01M)
                    afterBalance = 0;
                sbLog.AppendFormat(tpSqlHistory, key, beforeBalance, changeBalance, afterBalance);
            }

            sb.AppendFormat(" [ModifyTime]=getdate() WHERE [MemberAccount]='{0}' ", memberAccount);
            sb.Append(sbLog.ToString());

            return sb.ToString();
        }

        /// <summary>
        /// GetProperty 根据指定的属性名获取目标对象该属性的值
        /// </summary>
        private object GetProperty(object obj, string propertyName)
        {
            Type t = obj.GetType();

            return t.InvokeMember(propertyName, BindingFlags.Default | BindingFlags.GetProperty, null, obj, null);
        }

        public DataRow GetTradeFeeAwardStat(string memberAccount)
        {
             var ds = DbHelperSQL.Query(@"select 
                            isnull((select sum(ChangeBalance) from [dbo].[Member_AssetHistory] where MemberAccount=@MemberAccount and AssetType='TradeFeeAward'),0) as TotalAmount,
                            isnull((select count(0) from [dbo].[Member_Info] where Referrer=@MemberAccount),0) as TotalMember", new SqlParameter[] { new SqlParameter("@MemberAccount", memberAccount) });

             return CommonHelper.GetDataRow(ds);
        }
    }
}


  